import java.sql.*;
import java.util.*;

public class jdbc {
    //数据库管理

    //查询用户信息
    public List<User> getUserData(int id) throws SQLException, ClassNotFoundException {
        //加载驱动类
        //Class.forName("com.mysql.jdbc.Driver");
        String url="jdbc:mysql://localhost:3306/mob";
        String username="root";
        String password="105875";
        //获取连接对象
        Connection con = DriverManager.getConnection(url,username,password);
        String sql="select* from user where id=?;";
        //获取执行者---操作数据库
        PreparedStatement pstmt = con.prepareStatement(sql);
        ResultSet re=null;
        pstmt.setInt(1,id);
        List<User> list=new ArrayList<User>();
        try {
            //开启事务
            con.setAutoCommit(false);
            re=pstmt.executeQuery();
            //提交事务
            con.commit();
        } catch (Exception e) {
           //回滚事务
            con.rollback();
            throw new RuntimeException(e);
        }
        User user=null;
        while(re.next()){
            user=new User();
            user.setId(re.getInt("id"));
            user.setName(re.getString("name"));
            user.setPassword(re.getString("password"));
            list.add(user);
        }
        return list;
    }

    //添加用户
    public int addUser(String name,String pwd) throws SQLException, ClassNotFoundException {
        if(pwd==null)
            return 0;
        //加载驱动类
        String url="jdbc:mysql://localhost:3306/mob";
        String username="root";
        String password="105875";
        //获取连接对象
        Connection con = DriverManager.getConnection(url,username,password);
        //获取执行者---操作数据库
        String sql ="insert into user(name,password) values(?,?);";
        PreparedStatement pstmt = con.prepareStatement(sql);
        pstmt.setString(1,name);
        pstmt.setString(2,pwd);
        int re;
        try {
            //开启事务
            con.setAutoCommit(false);
            //执行
            re=pstmt.executeUpdate();
            //提交事务
            con.commit();
        } catch (Exception e) {
            //回滚事务
            con.rollback();
            throw new RuntimeException(e);
        }
        return re;//受影响行数
    }
    //删除用户
    public int deleteUser(int id) throws SQLException, ClassNotFoundException {
        String url="jdbc:mysql://localhost:3306/mob";
        String username="root";
        String password="105875";
        Connection con = DriverManager.getConnection(url,username,password);
        //删除用户--操作数据库
        String sql ="delete from user where id=?";
        PreparedStatement pstmt = con.prepareStatement(sql);
        pstmt.setInt(1,id);
        int re;
        try {
            //开启事务
            con.setAutoCommit(false);
            //执行
            re=pstmt.executeUpdate();
            //提交事务
            con.commit();
        } catch (Exception e) {
            //回滚事务
            con.rollback();
            throw new RuntimeException(e);
        }
        return re;
    }
    //修改信息
    public int updatePerson(int id,String name,String pwd) throws SQLException, ClassNotFoundException {
        if(pwd==null){//不允许密码为空
            return 0;
        }
        String url="jdbc:mysql://localhost:3306/mob";
        String username="root";
        String password="105875";
        Connection con = DriverManager.getConnection(url,username,password);
        String sql ="update user set name=?,password=? where id=?";
        //更新用户--操作数据库
        PreparedStatement pstmt = con.prepareStatement(sql);
        pstmt.setString(1,name);
        pstmt.setString(2,pwd);
        pstmt.setInt(3,id);
        int re;
        try {
            //开启事务
            con.setAutoCommit(false);
            //执行
            re=pstmt.executeUpdate();
            //提交事务
            con.commit();
        } catch (Exception e) {
            //回滚事务
            con.rollback();
            throw new RuntimeException(e);
        }
        return re;
    }
}
